package com.baturu.vin.benz.cleanData;

import com.baturu.vin.benz.BaseTest;
import com.baturu.vin.benz.cleanData.constants.TableEnum;
import com.baturu.vin.benz.cleanData.constants.TableInfo;
import com.baturu.vin.benz.cleanData.dao.BenzCarTypeDAO;
import com.baturu.vin.benz.cleanData.dao.BenzCleanDAO;
import com.baturu.vin.benz.cleanData.dao.BenzDealInfoDAO;
import com.baturu.vin.benz.cleanData.dto.ColumnDTO;
import com.baturu.vin.benz.cleanData.dto.TableDTO;
import com.baturu.vin.benz.dto.BenzDealInfoDTO;
import com.baturu.vin.benz.service.impl.util.BenzStringUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static java.util.stream.Collectors.groupingBy;

/**
 * @Author: jiangxinlei
 * @Time: 2018/2/5 14:45
 **/
@Slf4j
public class BenzAutoCleanData extends BaseTest {

    @Autowired
    private BenzCleanDAO cleanDAO;
    @Autowired
    private BenzCarTypeDAO carTypeDAO;
    @Autowired
    private BenzDealInfoDAO benzDealInfoDAO;

    protected static List<String> dc_rtype1_v_tables = Lists.newArrayList(TableEnum.DC_RTYPE1_V_DAG_DCCV.getName(),
            TableEnum.DC_RTYPE1_V_DAG_DCPC.getName(),
            TableEnum.DC_RTYPE1_V_DCEX_DCEX.getName(),
            TableEnum.DC_RTYPE1_V_MBJA_DCJA.getName(),
            TableEnum.DC_RTYPE1_V_MBLA_DCLA.getName(),
            TableEnum.DC_RTYPE1_V_MBNA_DCNA.getName(),
            TableEnum.DC_RTYPE1_V_MBOE_DCCAR.getName(),
            TableEnum.DC_RTYPE1_V_MBOE_DCGWGN.getName(),
            TableEnum.DC_RTYPE1_V_MBSM_DCSM.getName());

    private Map<String, List<BenzDealInfoDTO>> displacementMap;
    private Map<String, List<BenzDealInfoDTO>> transmissionMap;
    private Set<String> displacementSet;
    private Set<String> transmissionSet;

    @Test
    public void test(){
        log.info("123"+new Date());
    }

    @Test
    public void cleanData() throws Exception {
        //新建表
        createTables();
        //清洗数据
        List<TableDTO> commonTables = TableInfo.getCommonTables();
        for (TableDTO table : commonTables) {
            try {
                dealCommonData(table);
            } catch (Exception e) {
                log.error("有问题,快解决");
                break;
            }
        }

        //benz_parts_mapping_ext
        cleanDAO.createPartsMappingExt();
        cleanDAO.insertPartsMappingExt();

        buildCarType();
        Thread t2 = new Thread(() -> {
            try {
                new BenzPartsCodeDealTest().combineCodeB();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });


        Thread t1 = new Thread(() -> {
            try {
                new BenzImageOutput().getImageCalloutPosition();
            } catch (Exception e) {
                e.printStackTrace();
            }
        });
        t1.start();
        t2.start();
        t1.join();
        t2.join();
        new BenzDealPartsDesc().dealPartsDesc();

    }


    private void dealTmpBenzCarTypeAll() {
        log.info("创建tmp_benz_carType,时间:{}",new Date());
        carTypeDAO.dropTmpBenzCarTypeAll();
        carTypeDAO.createTmpBenzCarTypeAll();

        dc_rtype1_v_tables.forEach(tableName->{
            String newTable = tableName+"_newdata";
            String type3Table = newTable.replaceAll("rtype1","rtype3");
            log.info("插入{}和{}数据,时间:{}",newTable,type3Table,new Date());
            carTypeDAO.insertTmpBenzCarTypeAll(newTable,type3Table);
        });

        //加入sacode是为了之后的排量和变速箱做准备,但会导致数据量很大...因此优先处理掉sacode字段
        List<BenzDealInfoDTO> benzDealInfoDTOS = benzDealInfoDAO.queryAllSaDescription();
        displacementMap = benzDealInfoDTOS.stream().filter(p->StringUtils.isNotBlank(p.getAdesc())).filter(p->p.getAdesc().contains("排量")).collect(groupingBy(BenzDealInfoDTO::getSaCode));
        displacementSet = displacementMap.keySet();
        transmissionMap = benzDealInfoDTOS.stream().filter(p->StringUtils.isNotBlank(p.getAdesc())).filter(p->p.getAdesc().contains("变速")).collect(groupingBy(BenzDealInfoDTO::getSaCode));
        transmissionSet = transmissionMap.keySet();

        int maxId = carTypeDAO.maxId();
        for (int i=0; i<=maxId; i += 1000) {
            List<Map<String, String>> allData = carTypeDAO.queryTmpBenzCarTypeAllById(i, i + 1000);

            List<Map<String, String>> updateList = Lists.newArrayListWithExpectedSize(allData.size());
            for (Map<String, String> p : allData) {
                String id = String.valueOf(p.get("id"));
                String saCode = p.get("saCode");

                String aDate;
                String releaseDate= StringUtils.replace(p.get("release_date")," ","");
                String deliveryDate = StringUtils.replace(p.get("delivery_date")," ","");
                aDate = releaseDate;
                if (StringUtils.isBlank(releaseDate) ||
                        (StringUtils.isNotBlank(deliveryDate) && releaseDate.length()<6 && deliveryDate.length()>=6)) {
                    aDate = deliveryDate;
                }
                if (StringUtils.isBlank(aDate)) {
                    continue;
                }
                if (aDate.length()>=8) {
                    aDate = aDate.substring(2,8);
                }
                if(aDate.length()<6) {
                    continue;
                }

                if (Integer.valueOf(aDate.substring(0,2))<60){
                    aDate = "20" + aDate;
                } else {
                    aDate = "19" + aDate;
                }


                Set<String> saCodeSet = BenzStringUtil.getSaCodeSet(Lists.newArrayList(saCode));

                Set<String> vinTypes = Sets.newHashSet();
                dealAllType(vinTypes, p.get("chassis"));
                dealAllType(vinTypes, p.get("engine"));
                dealAllType(vinTypes, p.get("transmission"));

                Set<String> disresult = Sets.newHashSet();
                disresult.clear();
                disresult.addAll(displacementSet);
                disresult.retainAll(saCodeSet);
                //说明有排量
                Set<String> disInfo = Sets.newHashSet();
                if(CollectionUtils.isNotEmpty(disresult)) {
                    Set<String> descWithoutType = Sets.newHashSet();
                    Set<String> descWithoutDate = Sets.newHashSet();
                    for (String q : disresult) {
                        List<BenzDealInfoDTO> dtos = displacementMap.get(q);
                        for (BenzDealInfoDTO dto : dtos) {
                            if (StringUtils.isBlank(dto.getAdate())) {
                                descWithoutDate.add(dto.getAdesc());
                                continue;
                            }
                            if (Integer.valueOf(aDate)<Integer.valueOf(dto.getAdate())) {
                                continue;
                            }
                            if (StringUtils.isBlank(dto.getType())) {
                                descWithoutType.add(dto.getAdesc());
                                continue;
                            }
                            if (!vinTypes.contains(dto.getType())){
                                continue;
                            }
                            disInfo.add(dto.getAdesc());
                        }
                    }
                    if (CollectionUtils.isEmpty(disInfo)) {
                        Set<String> desc = CollectionUtils.isNotEmpty(descWithoutType)? descWithoutType:descWithoutDate;
                        disInfo.addAll(desc);
                    }
                }

                Set<String> transresult = Sets.newHashSet();
                transresult.clear();
                transresult.addAll(transmissionSet);
                transresult.retainAll(saCodeSet);
                Set<String> transInfo = Sets.newHashSet();
                if(CollectionUtils.isNotEmpty(transresult)) {
                    Set<String> descWithoutType = Sets.newHashSet();
                    Set<String> descWithoutDate = Sets.newHashSet();
                    for (String q : transresult) {
                        List<BenzDealInfoDTO> dtos = transmissionMap.get(q);
                        for (BenzDealInfoDTO dto : dtos) {
                            if (StringUtils.isBlank(dto.getAdate())) {
                                descWithoutDate.add(dto.getAdesc());
                                continue;
                            }
                            if (Integer.valueOf(aDate)<Integer.valueOf(dto.getAdate())) {
                                continue;
                            }
                            if (StringUtils.isBlank(dto.getType())) {
                                descWithoutType.add(dto.getAdesc());
                                continue;
                            }
                            if (!vinTypes.contains(dto.getType())){
                                continue;
                            }
                            transInfo.add(dto.getAdesc());
                        }
                    }
                    if (CollectionUtils.isEmpty(transInfo)) {
                        Set<String> desc = CollectionUtils.isNotEmpty(descWithoutType)? descWithoutType:descWithoutDate;
                        transInfo.addAll(desc);
                    }
                }

                String dis = "";

                if (CollectionUtils.isNotEmpty(disInfo)) {
                    for (String s : disInfo) {
                        Pattern pattern = Pattern.compile("([0-9.]+)");
                        Matcher matcher = pattern.matcher(s);
                        if (matcher.find()) {
                            dis = matcher.group(0);
                            break;
                        }
                    }
                }

                String transmissionType = "";
                String transmissionDesc = "";
                String gearsNum = "";

                if (CollectionUtils.isNotEmpty(transInfo)) {
                    for (String s : transInfo) {
                        if (s.contains("自动")) {
                            transmissionType = "自动";
                            transmissionDesc="自动变速器(AT)";
                        } else if (s.contains("手动")) {
                            transmissionType = "手动";
                            transmissionDesc="手动变速器(MT)";
                        }

                        Pattern pattern = Pattern.compile("(\\d)+(\\s[档])");
                        Matcher matcher = pattern.matcher(s);
                        if (matcher.find()) {
                            gearsNum = matcher.group(1)+"档";
                            break;
                        }
                    }
                }

                if (StringUtils.isNotBlank(dis) || StringUtils.isNotBlank(transmissionType) || StringUtils.isNotBlank(gearsNum)) {
                    Map<String, String> dealDisTrans = Maps.newHashMap();
                    dealDisTrans.put("id", id);
                    dealDisTrans.put("displacement", dis);
                    dealDisTrans.put("transmissionType", transmissionType);
                    dealDisTrans.put("transmissionDesc",transmissionDesc);
                    dealDisTrans.put("gearsNum", gearsNum);
                    updateList.add(dealDisTrans);
                }
            }
            if (CollectionUtils.isNotEmpty(updateList)) {
                log.info("更新车型数据,id起始:{},时间:{}",i,new Date());
                carTypeDAO.dealDisAndTrans(updateList);
            }
        }
    }


    @Test
    public void sdsadasfwsfsdg() throws Exception {
        Set<String> ss = Sets.newHashSet();
        String str = "1234";
        dealAllType(ss, str);
        System.out.println(ss);
    }

    private void dealAllType(Set<String> type, String str) {
        if(str != null) {
            str += "       ";
            type.add(str.substring(0, 3));
            type.add(str.substring(0, 3)+"."+str.substring(3,4));
            type.add(str.substring(0, 3)+"."+str.substring(3,5));
            type.add(str.substring(0, 3)+"."+str.substring(3,6));
        }
    }

    private void buildCarType() {

        dealTmpBenzCarTypeAll();


        log.info("创建tmp_benz_carType,时间:{}",new Date());
        carTypeDAO.dropTmpBenzCarType();
        carTypeDAO.createTmpBenzCarType();
        carTypeDAO.insertTmpBenzCarType();

        log.info("处理tmp_benz_carType数据,时间:{}",new Date());
        carTypeDAO.updateReleaseDate();
        carTypeDAO.deleteNoReleaseDate();
        carTypeDAO.dealReleaseDate1();
        carTypeDAO.dealReleaseDate2();
        carTypeDAO.deleteReleaseDate();

        log.info("创建tmp_benz_carType_ex,时间:{}",new Date());
        carTypeDAO.dropCarTypeEx();
        carTypeDAO.createCarTypeEx();
        carTypeDAO.insertCarTypeEx();
        carTypeDAO.updateYear1();
        carTypeDAO.updateYear2();
        carTypeDAO.updateCountry1();
        carTypeDAO.updateCountry2();
        carTypeDAO.updateCountry3();
        carTypeDAO.updateCountry4();
        carTypeDAO.updateCountry5();
        carTypeDAO.updateAppInf1();
        carTypeDAO.updateAppInf2();
        carTypeDAO.updateAppInf3();
        carTypeDAO.updateAppInf4();
        carTypeDAO.updateAppInf5();
        carTypeDAO.updateCarType();
        carTypeDAO.updateEngine();
        carTypeDAO.updateTrans();

        log.info("创建tmp_benz_vininfo_btr,时间:{}", new Date());
        carTypeDAO.dropTmpBenzVinInfoBtr();
        carTypeDAO.createVinBtr();
        carTypeDAO.insertVinBtr();
        carTypeDAO.updateNullToEmpty1();
        carTypeDAO.updateNullToEmpty2();
        carTypeDAO.updateNullToEmpty3();
        carTypeDAO.updateExistsBtr();

        List<String> ids = carTypeDAO.needDealBtr();
        if (CollectionUtils.isEmpty(ids)) {
            log.info("没有新增的车型");
            return;
        }
        // 序列号起始位
        AtomicInteger btrSerial = new AtomicInteger(1);

        SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
        String yyyy = df.format(new Date());

        String btrPre = "BENZEPC"+yyyy;

        List<Map<String, String>> btrMap = Lists.newArrayList();
        for (String id : ids) {
            String btrId;
            synchronized (btrSerial) {
                btrId = String.format("%s%07d", btrPre, btrSerial.getAndIncrement());
            }
            Map<String, String> map = Maps.newHashMap();
            map.put("id", id);
            map.put("btrId", btrId);
            btrMap.add(map);
        }

        carTypeDAO.updateBtr(btrMap);

        log.info("创建tmp_qp_cartype_benz,时间:{}", new Date());
        carTypeDAO.dropQpCarType();
        carTypeDAO.createQpCarType();
        carTypeDAO.insertQpCarType(btrPre);
        carTypeDAO.deleteNoCountry();

        List<Map<String, String>> tmpCarSystem = carTypeDAO.queryTmpCarSystem();
        List<Map<String, String>> tmpCarType = carTypeDAO.queryTmpCarType();

        List<Map<String, String>> btrCarsystemMap = Lists.newArrayList();

        for (Map<String, String> tmp : tmpCarType) {
            String btrId = tmp.get("btrId");
            String btrCountry = tmp.get("btrCountry");
            String carType = tmp.get("carType");
            String carTypeShort = carType.contains("AMG")?"AMG":"";
            String chassis = tmp.get("chassis");

            String chassPre = chassis.substring(0, 3);

            Optional<Map<String, String>> first = tmpCarSystem.stream().filter(p -> p.get("btrCountry").equalsIgnoreCase(btrCountry)
                    && p.get("carType").equalsIgnoreCase(carTypeShort) && p.get("chassis").equalsIgnoreCase(chassPre))
                    .findFirst();

            if (first.isPresent()) {
                Map<String, String> hhMap = Maps.newHashMap();
                hhMap.putAll(first.get());
                hhMap.put("btrId", btrId);
                btrCarsystemMap.add(hhMap);
            }
        }

        carTypeDAO.updateCarSystem(btrCarsystemMap);


        carTypeDAO.updateDriveType();
        carTypeDAO.updatexxx();
        carTypeDAO.updateCountry();
        carTypeDAO.updateBTRTrans();
        carTypeDAO.updateBTRTrans2();
        carTypeDAO.updateBTRTrans3();
        carTypeDAO.updateSalesName();

    }




    private void createTables() {
        for (TableEnum tableEnum : TableEnum.values()) {
            cleanDAO.createTable(tableEnum.getName());
        }
    }

    private void dealCommonData(TableDTO tableDTO) throws ClassNotFoundException, SQLException {
        Class.forName("transbase.jdbc.Driver");
        String dbUrl = "jdbc:transbase://192.168.16.192:2034/" + tableDTO.getDataBaseName();
        String userName = "tbadmin";
        String password = "LMtdKNkhDHakNR3";
        Connection conn = DriverManager.getConnection(dbUrl, userName, password);

        String spoolSql = tableDTO.getQuerySql();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(spoolSql);
        if (rs == null) {
            stmt.close();
            conn.close();
            return;
        }

        List<ColumnDTO> columns = tableDTO.getColumns();

        String insertColumn = "";
        for (int i = 0; i < columns.size(); i ++) {
            insertColumn = insertColumn + "`"+columns.get(i).getDesColumn()+"`" + ",";
        }
        insertColumn = insertColumn.substring(0, insertColumn.length() - 1);

        StringBuilder sb = new StringBuilder();
        int count = 0;
        while (rs.next()) {
            count ++;
            sb.append("(");
            for (int i = 0; i < columns.size(); i ++) {
                String value = rs.getString(columns.get(i).getOriginColumn());
                sb.append(deal(value)).append(",");
            }
            sb.deleteCharAt(sb.length()-1);
            sb.append("),");

            if (count % 1000 == 0 && sb.length()>1) {
                log.info("清洗表 "+tableDTO.getDesTable()+" 共"+count+"条数据,时间"+(new Date()));
//                System.out.println("清洗表 "+tableDTO.getDesTable()+" 共"+count+"条数据,时间"+(new Date()));
                sb.deleteCharAt(sb.length() - 1);
                cleanDAO.insertCommon(tableDTO.getDesTable(), insertColumn, sb.toString());
                sb = new StringBuilder();
            }
        }
        if (StringUtils.isNotBlank(sb) && sb.length()>1) {
            log.info("清洗表 "+tableDTO.getDesTable()+" 共"+count+"条数据,时间"+(new Date()));
//            System.out.println("清洗表 "+tableDTO.getDesTable()+" 共"+count+"条数据,时间"+(new Date()));
            sb.deleteCharAt(sb.length() - 1);
            cleanDAO.insertCommon(tableDTO.getDesTable(), insertColumn, sb.toString());
        }
        rs.close();
        stmt.close();
        conn.close();
    }

    private String deal(String str) {
        if (StringUtils.isBlank(str)) {
            return "null";
        }
        String replace = StringUtils.replace(str, "\'", "\\\'");
        return "'"+ replace +"'";
    }

}
